﻿
CREATE FUNCTION [dbo].[Collection_PhoneWork](
	@UsixID varchar(8),
	@CompanyID int,
	@Pos int
)RETURNS TABLE 
RETURN 
SELECT    REPLACE(ISNULL(E.Phone, CF_E.[Work Phone #]), '-', '') AS PhoneWork,'W' PhoneType
FROM         t_Loan_Borrower AS L_B INNER JOIN
                      t_Employment AS E ON L_B.BorrowerID = E.BorrowerID RIGHT OUTER JOIN
                      usix.t_CustomerFile_Export AS CF_E ON L_B.LoanID = CF_E.ApplID AND (L_B.Pos = 0)
WHERE     (CF_E.UsixID = @UsixID) AND (CF_E.CompanyID = @CompanyID) AND @Pos = 0
UNION
SELECT     REPLACE(ISNULL(E.Phone, CF_E.[Joint W Phone#]), '-', '') AS PhoneWork,'WC'
FROM         t_Loan_Borrower AS L_B INNER JOIN
                      t_Employment AS E ON L_B.BorrowerID = E.BorrowerID RIGHT OUTER JOIN
                      usix.t_CustomerFile_Export AS CF_E ON L_B.LoanID = CF_E.ApplID AND (L_B.Pos = 1)
WHERE     (CF_E.UsixID = @UsixID) AND (CF_E.CompanyID = @CompanyID) AND @Pos = 1
AND	(REPLACE(ISNULL(E.Phone, CF_E.[Joint W Phone#]), '-', '') > '')



